/*
 * StudentAddressDb.cpp
 *
 *  Created on: 24 Apr 2010
 *      Author: Vastsor
 */

#include "StudentAddressDb.h"
StudentAddressDb::StudentAddressDb() {

}

StudentAddressDb::StudentAddressDb(const string *dbName, const string *user, const string *passwd) {
	//OdbcConnectionSingleton::getInstance()->connect(dbName,user,passwd);
	if(OdbcConnectionSingleton::getInstance()->connect(dbName,user,passwd)) {

	}
	else throw;
}



StudentAddressDb::~StudentAddressDb() {
	// TODO Auto-generated destructor stub
}

bool
StudentAddressDb::saveId(const string *id) const {
	bool returnValue=false;
	SQLRETURN rc;

	string sqlString="INSERT INTO StudentAddresses (id) values('"+*id+"')";
	rc = SQLExecDirect(OdbcConnectionSingleton::getInstance()->getStmtHandle(), (SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS){
				returnValue=true;
				}


return returnValue;
}



bool
StudentAddressDb::saveTown(const string *id, const string *town)const {
	bool returnValue=false;
	SQLRETURN rc;

	string sqlString="UPDATE StudentAddresses set town= '"+*town+"' where id ='"+*id+"'";
	rc = SQLExecDirect(OdbcConnectionSingleton::getInstance()->getStmtHandle(), (SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS){
			returnValue=true;
			}

return returnValue;
}
bool
StudentAddressDb::saveStreetName(const string *id, const string *streetName)const  {
	bool returnValue=false;
	SQLRETURN rc;
	string sqlString="UPDATE StudentAddresses set streetName= '"+*streetName+"' where id='"+*id+"'";

	rc = SQLExecDirect(OdbcConnectionSingleton::getInstance()->getStmtHandle(), (SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS){
			returnValue=true;
			}
return returnValue;
}
bool
StudentAddressDb::saveStreetNumber(const string *id, const string *streetNumber)const {
	bool returnValue=false;
	SQLRETURN rc;

	string sqlString="UPDATE StudentAddresses set streetNumber= '"+*streetNumber+"' where id='"+*id+"'";
	rc = SQLExecDirect(OdbcConnectionSingleton::getInstance()->getStmtHandle(), (SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS){
		returnValue=true;
		}

return returnValue;
}

string *
StudentAddressDb::getLastId() const {

	string *lastId=0;
	if(!this->isEmpty()) {
		char cId[255];

		SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
		SQLRETURN rc;

		string sqlString="SELECT MAX(CAST(id AS SIGNED)) FROM StudentAddresses";
		rc = SQLExecDirect(stmtHandle, (SQLTCHAR *)sqlString.c_str(), SQL_NTS);
		if(rc==SQL_SUCCESS) {
			SQLBindCol(stmtHandle, 1, SQL_C_CHAR, (SQLPOINTER) cId, sizeof(cId), NULL);
			if((rc  = SQLFetch(stmtHandle))!= SQL_NO_DATA) {
				lastId=new string(cId);
				ConvertType *ct=new ConvertType();
				lastId=ct->toString(1+ct->toInt(lastId));
				delete ct;
			}
		}
	}
	else
		lastId=new string("1");
return lastId;
}






string *
StudentAddressDb::getIdByAddress(const string *streetName, const string *streetNumber, const string *town) const{
	char cAem[255];
	string *aem=NULL;

	SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
	SQLRETURN rc;

	string sqlString="SELECT id From StudentAddresses where streetName='"+*streetName+"'";
	sqlString+=" and streetNumber='"+*streetNumber+"' and town='"+*town+"'";
	rc = SQLExecDirect(stmtHandle, (SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS){
			//We know that the 1st column of the query we sent is the aem
				SQLBindCol(stmtHandle, 1, SQL_C_CHAR, (SQLPOINTER) cAem, sizeof(cAem), NULL);
				if((rc  = SQLFetch(stmtHandle))!= SQL_NO_DATA) {
							aem=new string(cAem);
							}
				}
return aem;
}

bool
StudentAddressDb::isEmpty() const {
	bool returnValue=true;
	char id[255];
	SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
	SQLRETURN rc;

	string sqlString="Select id From StudentAddresses";
	rc= SQLExecDirect(stmtHandle,(SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS) {

		SQLBindCol(stmtHandle,1,SQL_C_CHAR,(SQLPOINTER) id,sizeof(id),NULL);
		if((rc  = SQLFetch(stmtHandle))!= SQL_NO_DATA) {
				returnValue=false;

		}
	}
return returnValue;

}




string *
StudentAddressDb::getTown(const string *aem) const {
	char cTown[255];
	string *town=NULL;
	SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
	SQLRETURN rc;

	string sqlString="SELECT town From StudentAddresses INNER JOIN Students ON ";
	sqlString+="StudentA_id=id where aem='"+*aem+"'";

	rc = SQLExecDirect(stmtHandle,(SQLTCHAR *)sqlString.c_str(),SQL_NTS);
	if(rc==SQL_SUCCESS) {
		SQLBindCol(stmtHandle,1,SQL_C_CHAR,(SQLPOINTER) cTown,sizeof(cTown),NULL);
		if((rc  = SQLFetch(stmtHandle))!= SQL_NO_DATA) {
			town=new string(cTown);
		}

	}
return town;
}

string *
StudentAddressDb::getStreetName(const string *aem) const {
	char cName[255];
	string *name=NULL;
	SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
	SQLRETURN rc;

	string sqlString="SELECT streetName From StudentAddresses INNER JOIN Students ON ";
	sqlString+="StudentA_id=id where aem='"+*aem+"'";

	rc = SQLExecDirect(stmtHandle,(SQLTCHAR *)sqlString.c_str(),SQL_NTS);
	if(rc==SQL_SUCCESS) {
		SQLBindCol(stmtHandle,1,SQL_C_CHAR,(SQLPOINTER) cName,sizeof(cName),NULL);
		if((rc  = SQLFetch(stmtHandle))!= SQL_NO_DATA) {
			name=new string(cName);
		}

	}
return name;
}

string *
StudentAddressDb::getStreetNumber(const string *aem) const {
	char cNumber[255];
	string *number=NULL;
	SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
	SQLRETURN rc;

	string sqlString="SELECT streetNumber From StudentAddresses INNER JOIN Students ON ";
	sqlString+="StudentA_id=id where aem='"+*aem+"'";

	rc = SQLExecDirect(stmtHandle,(SQLTCHAR *)sqlString.c_str(),SQL_NTS);
	if(rc==SQL_SUCCESS) {
		SQLBindCol(stmtHandle,1,SQL_C_CHAR,(SQLPOINTER) cNumber,sizeof(cNumber),NULL);
		if((rc  = SQLFetch(stmtHandle))!= SQL_NO_DATA) {
			number=new string(cNumber);
		}

	}
return number;
}

bool
StudentAddressDb::saveStudentAddress(const string *aem, const string *id) const {
	bool returnValue=false;
	SQLHANDLE stmtHandle=OdbcConnectionSingleton::getInstance()->getStmtHandle();
	SQLRETURN rc;

	string sqlString="UPDATE Students set StudentA_id='"+*id+"' where aem='"+*aem+"'";
	rc= SQLExecDirect(stmtHandle,(SQLTCHAR *)sqlString.c_str(), SQL_NTS);
	if(rc==SQL_SUCCESS) {
		 returnValue=true;
		}
return returnValue;
}


